import platform = require('../common/baseCommon');
import data = require('./data');
import kfjsonrpc = require('./jsonRpc/jsonRpc');
import orm = require('typeorm');
import "reflect-metadata";
import { baseService, format } from '../common/baseCommon';
import { decode } from 'punycode';



const moduleName = "platform.server";

/**
 * 默认数据库名称
 */
const defaultDatabaseName = "default";

/**
 * sql 字典
 */
export class sqlMap {
    /**
     * sql 语句
     */
    sql: string;

    /**
     * 参数必须为数组
     */
    params: any;
}




/**
 * 连接信息 主要保存数据库的连接信息
 */
@platform.addon("数据库连接对象", "用于保存数据库连接的对象", moduleName)
export class connectionInfo extends platform.baseObject {

    /**
     * 
     * @param id 连接ID
     * @param url 数据库地址
     * @param user 用户名
     * @param pwd 密码
     * @param driver 驱动 
     */
    constructor(id: string, url: string, user: string, pwd: string, driver: string) {
        super();
        this.connectID = id;
        this.dataUrl = url;
        this.userName = user;
        this.password = pwd;
        this.driverClass = driver;
    }

    /**
     * 连接器ID
     */
    connectID: string;

    /**
     * 描述
     */
    describe: string;

    /**
     * 数据库连接地址
     */
    dataUrl: string;

    /**
     * 用户名
     */
    userName: string;

    /**
     * 数据库的密码
     */
    password: string;

    /**
     * 数据库驱动类名
     */
    driverClass: string;

    /**
     * 线程池初始化连接数
     */
    initialPoolSize: number = 3;


    /**
     * 线程池最少连接数
     */
    minPoolSize: number = 3;

    /**
     * 线程池最大连接数 
     */
    maxPoolSiz: number = 15;

    /**
     * 配置PreparedStatement缓存
     */
    maxStatements: number = 0;

    /**
     * 最大空闲时间（单位为秒）
     */
    maxIdleTime: number = 60;
}


@platform.addon("数据连接信息以及命令", "数据连接信息以及命令", moduleName)
export class dbInfoAndConnands extends platform.baseObject {

    /**
     * 连接信息
     */
    connectInfo: connectionInfo;

    /**
     * 数据连接信息
     */
    commandList: data.dataCommandInfo[];

    /**
     * 分页查询语句(默认值未orcle 分页语句)
     */
    //pagingQuerySql: string = 'SELECT * FROM (SELECT TR.*,ROWNUM RN FROM({0}) TR WHERE ROWNUM <= {1}) TF WHERE TF.RN > {2}';



    /**
   * 获取命令信息
   * @param commandID 命令ID
   */
    getCommandByID?(commandID: string): data.dataCommandInfo {
        if (this.commandList) {
            for (let cmd of this.commandList) {
                if (cmd.id == commandID) {
                    return cmd;
                }
            }
        }
        return undefined;
    }

}



/**
 * 数据连接器接口，主要作用调用远程数据连接执行指令
 */
@platform.addon("SQL执行器", "sql执行器", moduleName)
export class IExecuteSQLForNode extends platform.baseObject {

    constructor() {
        super();
    };

    /**
     * 获取表信息
     * @param tableName  表名
     */
    getTableMetaData(tableName: string): Promise<any> {
        return null;
    }


    /**
     * 查询数据条数
     * @param connInfo 连接信息 
     * @param sql 查询的sql 语句
     * @param paramValues 参数
     */
    executeQueryCount(connInfo: connectionInfo, sql: string, paramValues: {} | any): Promise<any> {
        return null;
    }

    /**
     * 查询数据（返回是一个dataTable）
     * @param connInfo 连接信息
     * @param sql sql
     * @param paramValues 参数值 
     */
    executeQuerydataTable(connInfo: connectionInfo, sql: string, paramValues: {} | any): Promise<any> {
        return null;
    }

    /**
     * 更新表的操作（返回更新条总数）
     * @param connInfo 连接信息 
     * @param sqlArray 操作的sql数组,包括sql语句与参数
     * 
     */
    executeUpdate(connInfo: connectionInfo, sqlArray: any): Promise<any> {
        return null;
    }

    /**
     * 获取表格信息
     * @param connInfo  连接信息
     * @param tableName 表名
     */
    getDataTableInfo(connInfo: connectionInfo,
        tableName: String): Promise<any> {
        return null;
    }
}

/**
 * dml 语句生成器 基类
 */
@platform.addon("dml生成器", "dml生成器", moduleName)
export class baseDmlStatementGenerator extends platform.baseObject {


    /**
     * 分页语句，默认为orcel 分页语句
     */
    pagingQuerySql: string = 'SELECT * FROM (SELECT TR.*,ROWNUM RN FROM({0}) TR WHERE ROWNUM <= {1}) TF WHERE TF.RN > {2}';


    /**
     * 插入sql 语句 默认为orcel
     */
    insertSql: string = 'insert into {0} ({1}) values ({2})';

    /**
     * 更新sql 语句 ，默认为orcel
     */
    updateSql: string = 'update {0} set {1} where {2}';

    /**
     * 删除的SQL 语句默认支持orcel
     */
    deleteSql: string = 'delete from {0} where {1}';


    constructor() {
        super();
    }


    /**
     * 获取查询数据的总条数
     * @param querysql 
     */
    generateQueryStatementCount(querysql: string): string {
        var countsql = 'SELECT count(*) FROM {0}';
        return platform.format(countsql, querysql);
    }

    /**
     * 查询sql 生成函数
     * @param querysql 查询sql 
     * @param start  开始的位置
     * @param count  查询的条数
     */
    generateQueryStatement(querysql: string, start: number, count: number): string {
        let maxindex = start + count;
        let res = platform.format(this.pagingQuerySql, querysql, maxindex, start);
        return res;
    }




    /**
     * 生成dml 语句
     * @param tableName  表名
     * @param table  table
     */
    generateDMLStatement(table: platform.dataTable): sqlMap[] {
        let sqlArray: sqlMap[] = [];

        //遍历每一行
        for (var i = 0; i < table.rows.length; i++) {
            let itemsql: sqlMap = null;
            switch (table.rows[i].dataRowStatus) {
                case platform.dataRowState.added:
                    itemsql = this.generateInsertDML(table, i);
                    break;
                case platform.dataRowState.deleted:
                    itemsql = this.generateDeleteDML(table, i);
                    break;
                case platform.dataRowState.Modified:
                    itemsql = this.generateUpdateDML(table, i);
                    break;
                default:
                    break;
            }
            if (itemsql != null)
                sqlArray.push(itemsql);
        }
        return sqlArray;
    }

    /**
     *  生成修改sql语句
     * @param table 表
     * @param rowIndex 行号
     */
    generateUpdateDML(table: platform.dataTable, rowIndex: number): sqlMap {
        let sqlmap = new sqlMap();
        var dml = 'update {0} set {1} where {2}';
        var params = [];
        //修改参数字符串
        let paramsModifyStr = '';
        //主键字符串
        let primaryKeyStr = '';



        //判断主键是否为空
        if (table.primaryKey == undefined) {
            throw new Error('主键为空');
        }

        //遍历所有行
        for (let key in table.columns) {
            let column = <platform.dataColumn>table.columns[key];
            paramsModifyStr += (column.name + '=?,');
            params.push((table.rows[rowIndex].rowValues)[key]);
            // if ((table.rows[rowIndex].rowValues)[key] != (table.rows[rowIndex].rowOriginalValue)[key]) {
            // }
        }
        //去掉最后一个逗号
        paramsModifyStr = paramsModifyStr.substr(0, paramsModifyStr.length - 1);
        //遍历所有主键
        for (var i = 0; i < table.primaryKey.length; i++) {
            let primaryKey = table.primaryKey[i]
            primaryKeyStr += primaryKey + '=?';
            params.push((table.rows[rowIndex].rowOriginalValue)[primaryKey]);
            if (i != table.primaryKey.length - 1) {
                primaryKeyStr += ' and '
            }

        }

        //生成最终sql语句 与参数
        sqlmap.sql = platform.format(dml, table.name, paramsModifyStr, primaryKeyStr);
        sqlmap.params = params;

        return sqlmap;
    }

    /**
     * 生成插入的sql语句
     * @param table 表
     * @param row 行号
     */
    generateInsertDML(table: platform.dataTable, rowIndex: number): sqlMap {

        let sqlmap = new sqlMap();
        var params = [];
        var dml = 'insert into {0} ({1}) VALUES ({2})';
        //列字符串
        let columnStr = '';

        //参数值字符串
        let valueStr = '';

        //遍历所有行的数据
        for (let key in table.columns) {
            let column = <platform.dataColumn>table.columns[key];
            columnStr += key + ',';
            valueStr += '?,';
            params.push((table.rows[rowIndex].rowValues)[key]);
        }
        columnStr = columnStr.substr(0, columnStr.length - 1);
        valueStr = valueStr.substr(0, valueStr.length - 1);
        sqlmap.sql = platform.format(dml, table.name, columnStr, valueStr);
        sqlmap.params = params;
        return sqlmap
    }

    /**
     * 生成删除sql语句
     * @param table 表
     * @param row 行号
     */
    generateDeleteDML(table: platform.dataTable, rowIndex: number): sqlMap {

        let sqlmap = new sqlMap();
        var params = [];
        var dml = 'delete from {0} where {1}';
        //条件
        let condition = '';
        //遍历所有主键并获取其值
        for (var i = 0; i < table.primaryKey.length; i++) {
            let primaryKey = table.primaryKey[i]
            condition += primaryKey + '=' + '?';
            params.push((table.rows[rowIndex].rowValues)[primaryKey]);
            if (i != table.primaryKey.length - 1) {
                condition += 'and'
            }

        }
        //根据主键生成删除语句
        sqlmap.sql = platform.format(dml, table.name, condition);
        sqlmap.params = params;
        return sqlmap;
    }

}

/**
 * mysql 生成器
 */
@platform.addon("mysql dml生成器", "mysql dml生成器", moduleName)
export class mysqlGenerator extends baseDmlStatementGenerator {

    constructor() {
        super();
        this.pagingQuerySql = '{0} LIMIT {1},{2}';
    }

    /**
     * 查询sql 生成函数
     * @param querysql 查询sql 
     * @param start  开始的位置
     * @param count  查询的条数
     */
    generateQueryStatement(sql: string, start: number, count: number): string {
        let querysql = platform.format(this.pagingQuerySql, sql, start, count);
        return querysql;

    }

}

/**
 *  访问数据库
 */
@platform.addon("数据服务", "数据服务", moduleName)
export class dataTableService extends platform.baseService implements platform.IDataService {


    /**
     * 数据库连接信息以及指令
     */
    //dbconnect: dbInfoAndConnands;

    /**
     * 数据库连接信息
     */
    dbConnectInfo: connectionInfo;


    /**
     * 数据命令
     */
    commandList: data.dataCommandInfo[] = [];


    /**
     * 第三方服务地址
     */
    serviceUrl: string;


    /**
     * sql 生成器
     */
    sqlGenerator: baseDmlStatementGenerator;



    /**
    * 获取命令信息
    * @param commandID 命令ID
    */
    getCommandByID?(commandID: string): data.dataCommandInfo {
        if (this.commandList) {
            for (let cmd of this.commandList) {
                if (cmd.id == commandID) {
                    return cmd;
                }
            }
        }
        return undefined;
    }


    /**
     * 获取远程服务工厂对象
     */
    getFactoryValue(): any {
        //数据库连接信息
        let dbconnect = this.dbConnectInfo

        //创建连接代理
        let dbFactory = new kfjsonrpc.jsonRpcFactory(IExecuteSQLForNode, this.serviceUrl, 'javaservice');

        //调用查询
        let executSQL = <IExecuteSQLForNode>dbFactory.getValue();

        delete dbconnect.$type;
        return executSQL;
    }


    /**
     * 获取查询的最大条数
     * @param command sql指令
     */
    queryCount(command: string, paramValues: {}): Promise<number> {
        //获取指令
        let cmd = this.getCommandByID(command);

        //清空过滤参数
        cmd.clearParameters();
        cmd.clearDataFilter();

        if (!cmd) {
            throw new Error(`找不到[${command}]的命令`);
        }

        // 生成待执行的SQL语句
        let executeSql = this.sqlGenerator.generateQueryStatementCount(cmd.getCommandText(paramValues));

        let executSQL = this.getFactoryValue();

        //获取参数
        let param = cmd.getParameters();

        //数据库连接信息
        let dbconnect = this.dbConnectInfo;


        return executSQL.executeQueryCount(dbconnect, executeSql, param).then(data => {
            if (data.error) {
                console.log(data.error);
                throw Error(data.error.message);
            }
            return data.result;
        });
    }



    /**
     * 查询数据库
     * @param command 
     * @param paramValues 
     * @param startIndex 
     * @param maxCount 
     */
    query(command: string, paramValues: {}, startIndex: number = 0, maxCount: number = 500): Promise<platform.dataTable> {

        //获取指令
        let cmd = this.getCommandByID(command);

        if (!cmd) {
            throw new Error(`找不到[${command}]的命令`);
        }

        //清空过滤参数
        cmd.clearParameters();
        cmd.clearDataFilter();

        // 生成待执行的SQL语句
        let executeSql = this.sqlGenerator.generateQueryStatement(cmd.getCommandText(paramValues), startIndex, maxCount);

        //获取参数
        let param = cmd.getParameters();

        //数据库连接信息
        let dbconnect = this.dbConnectInfo;

        //调用查询
        let executSQL = <IExecuteSQLForNode>this.getFactoryValue();

        //delete dbconnect.$type;
        if (cmd.isMultitableQuery) {
            //连表查询不用查询主键等参数
            return executSQL.executeQuerydataTable(dbconnect, executeSql, param).then(table => {
                if (table.error) {
                    console.log(table.error);
                    throw Error(table.error.message);
                }
                return this.kfDataTableToDataTable(table.result);
            })
        } else {
            //单表查询 如果没有配置主键就查询获取
            if (cmd.primaryKey != undefined) {
                return executSQL.executeQuerydataTable(dbconnect, executeSql, param).then(table => {
                    if (table.error) {
                        console.log(table.error);
                        throw Error(table.error.message);
                    }
                    let datatable = this.kfDataTableToDataTable(table.result);
                    datatable.primaryKey = cmd.primaryKey;
                    datatable.name = cmd.tableName;
                    return datatable;
                });
            } else {
                if (cmd.tableName != undefined) {

                    //因为获取主键时候表名不能有用户信息
                    let strArray = cmd.tableName.split('.');
                    let tableName = strArray[strArray.length - 1];
                    //let tableName = cmd.tableName;
                    //先根据表名查询主键
                    return executSQL.getDataTableInfo(dbconnect, tableName).then(tableInfo => {
                        if (tableInfo.result.keyField == null)
                            throw Error('查询主键失败');
                        return tableInfo.result.keyField;
                    }).then(primaryKey => {
                        //查询表
                        return executSQL.executeQuerydataTable(dbconnect, executeSql, param).then(table => {
                            if (table.error) {
                                console.log(table.error);
                                throw Error(table.error.message);
                            }
                            let datatable = this.kfDataTableToDataTable(table.result);
                            datatable.primaryKey = primaryKey;
                            datatable.name = cmd.tableName;
                            return datatable;
                        })
                    });
                } else {
                    throw Error('表名不存在');
                }
            }
        }
    }


    /**
     * 更新数据
     * @param datatable  datatable
     */
    updateTable(datatable: platform.dataTable): Promise<number> {

        if (datatable.name == undefined) {
            throw Error('表名不存在');
        }

        //数据库连接信息
        let dbconnect = this.dbConnectInfo;

        //调用查询
        let executSQL = <IExecuteSQLForNode>this.getFactoryValue();

        if (datatable.primaryKey == undefined) {
            // 生成待执行的SQL语句集合
            let executeSqlMap = this.sqlGenerator.generateDMLStatement(datatable);
            return executSQL.executeUpdate(dbconnect, executeSqlMap).then(data => {
                if (data.error) {
                    console.log(data.error);
                    throw Error(data.error.message);
                }
                return data.result;
            });
        } else {
            //查询主键
            //因为获取主键时候表名不能有用户信息
            let strArray = datatable.name.split('.');
            let tableName = strArray[strArray.length - 1];
            return executSQL.getDataTableInfo(dbconnect, tableName).then(tableInfo => {
                return tableInfo.result.keyField;
            }).then(primaryKey => {
                // 生成待执行的SQL语句集合
                let executeSqlMap = this.sqlGenerator.generateDMLStatement(datatable);
                //设置主键
                datatable.primaryKey = primaryKey;
                return executSQL.executeUpdate(dbconnect, executeSqlMap).then(data => {
                    if (data.error) {
                        console.log(data.error);
                        throw Error(data.error.message);
                    }
                    return data.result;

                })
            });

        }

    }

    /**
     * 查询主键
     * @param tableName 表名
     */
    queryTableInfo(tableName: string): Promise<platform.DataTableInfo> {
        //调用查询
        let executSQL = <IExecuteSQLForNode>this.getFactoryValue();
        //数据库连接信息
        let dbconnect = this.dbConnectInfo;

        //因为获取主键时候表名不能有用户信息
        let strArray = tableName.split('.');

        return executSQL.getDataTableInfo(dbconnect, strArray[strArray.length - 1]).then(data => {
            if (data.error) {
                console.log(data.error);
                throw Error(data.error.message);
            }
            return data.result;
        });

    }


    /**
     * java端dataTable 转换
     * @param kftable 
     */
    kfDataTableToDataTable(kftable: any): platform.dataTable {
        if (kftable == null) return;
        let dataTable = new platform.dataTable();
        //添加列
        for (var i = 0; i < kftable.columns.list.length; i++) {
            if (kftable.columns.list[i].ColumnName == 'RN')
                continue;
            let itemcolum = new platform.dataColumn();
            itemcolum.name = kftable.columns.list[i].ColumnName;
            itemcolum.caption = itemcolum.name;
            dataTable.addColumn(itemcolum);
        }

        //添加行数据
        dataTable.rows = [];
        //遍历所有行
        for (var i = 0; i < kftable.rows.list.length; i++) {
            let itemrow = new platform.dataRow();
            itemrow.rowValues = {};
            itemrow.rowOriginalValue = {};
            //遍历所有列
            for (var j = 0; j < kftable.rows.list[i].cells.length; j++) {
                var itemcell = kftable.rows.list[i].cells[j];
                let tempValue = itemcell['originalValue'];
                itemrow.rowValues[itemcell['columnName']] = tempValue;
                itemrow.rowOriginalValue[itemcell['columnName']] = tempValue;
            }
            dataTable.rows.push(itemrow);
        }
        return dataTable;
    }
    /** 获取表结构 */
    getTableSchema(command: string): Promise<platform.dataTable> {
        //获取指令
        let cmd = this.getCommandByID(command);

        //清空过滤参数
        cmd.clearParameters();
        cmd.clearDataFilter();

        if (!cmd) {
            throw new Error(`找不到[${command}]的命令`);
        }
        // 生成待执行的SQL语句
        let executeSql = cmd.getCommandText({});
        let filterSqlFormat = `select temp.* from ({0}) temp where 1=0`;
        executeSql = platform.format(filterSqlFormat, executeSql);
        //数据库连接信息
        let dbconnect = this.dbConnectInfo;
        let executSQL = this.getFactoryValue();

        return executSQL.executeQuerydataTable(dbconnect, executeSql, null).then(data => {
            if (data.error) {
                console.log(data.error);
                throw Error(data.error.message);
            }
            return this.kfDataTableToDataTable(data.result);
        });
    }
}

